﻿using OfficeOpenXml;
using System.IO;
using System.Collections.Generic;
using System.Linq;
using System.Data;
using System.Reflection;
using System.ComponentModel.DataAnnotations;
using System;
using System.Drawing;
using Microsoft.AspNetCore.Mvc.Rendering;
using System.Text.RegularExpressions;
using System.Globalization;

namespace Enter.Admin.Utils
{

    [Flags]
    public enum ExcelColumnType
    {
        台账表 = 1,
        诉讼信息表 = 1 << 1,
    }

    [AttributeUsage(AttributeTargets.Property, Inherited = true)]
    public class ExcelAttribute : Attribute
    {
        public ExcelAttribute()
        {
        }
        public ExcelColumnType ColumnType { get; set; }
        public string HeaderName { get; set; }
        public string[] HeaderNames { get; set; } = new string[] { };
        public string PropertyName { get; set; }
        public PropertyInfo Property { get; set; }
        // 下拉框
        public string Drop { get; set; } = string.Empty;
        // 是否必填
        public bool Required { get; set; }
        /// <summary>
        /// 导出公式
        /// </summary>
        public string Formula { get; set; } = string.Empty;
        /// <summary>
        /// 导出格式
        /// </summary>
        public string Format { get; set; } = string.Empty;
        // 导出排序
        public int Sort { get; set; }
        /// <summary>
        /// 是否导出
        /// </summary>
        public bool Export { get; set; } = true;
        /// <summary>
        /// 是否导入
        /// </summary>
        public bool Import { get; set; } = true;
        // 是否显示（不是导入导出用的）
        public bool Visible { get; set; } = true;
        // 是否可以搜索（不是导入导出用的）
        public bool Searchable { get; set; } = true;
        // 是否可以排序（不是导入导出用的）
        public bool Orderable { get; set; } = false;
        // 是否可以编辑
        public bool Editable { get; set; } = true;
    }

    public static class Excel//<T> where T : class
    {
        static Excel()
        {
            ExcelPackage.LicenseContext = LicenseContext.NonCommercial;
        }

        public static List<ExcelAttribute> Headers<T>()
        {
            var pds = typeof(T).GetProperties();
            // 获取头部.
            var headers = pds.Select(p =>
            {
                var attr = p.GetCustomAttribute(typeof(ExcelAttribute)) as ExcelAttribute;
                if (attr != null)
                {
                    attr.PropertyName = p.Name;
                    attr.Property = p;
                }
                return attr;
            }).Where(p => p != null).ToList();
            return headers;
        }

        public static List<ExcelAttribute> Headers<T,T2>()
        {
            var target_pds = typeof(T2).GetProperties();
            var pds = typeof(T).GetProperties();
            // 获取头部.
            var headers = pds.Select(p =>
            {
                var attr = p.GetCustomAttribute(typeof(ExcelAttribute)) as ExcelAttribute;
                if (attr != null)
                {
                    attr.PropertyName = p.Name;
                    attr.Property = target_pds.Where(t=>t.Name == p.Name).FirstOrDefault();
                }
                return attr;
            }).Where(p => p != null && p.Property != null).ToList();
            return headers;
        }

        public static DataTable ToTable(string filePath)
        {
            using (ExcelPackage package = new ExcelPackage(new FileInfo(filePath)))
            {

                ExcelWorksheet worksheet = package.Workbook.Worksheets.First(t => t.Hidden == eWorkSheetHidden.Visible);
                //获取worksheet的行数
                int rows = worksheet.Dimension.End.Row;
                //获取worksheet的列数
                int cols = worksheet.Dimension.End.Column;

                DataTable dt = new DataTable(worksheet.Name);
                DataRow dr = null;
                for (int i = 1; i <= rows; i++)
                {
                    if (i > 1)
                        dr = dt.Rows.Add();

                    for (int j = 1; j <= cols; j++)
                    {
                        //默认将第一行设置为datatable的标题
                        if (i == 1)
                            dt.Columns.Add(worksheet.Cells[i, j].Text);
                        //剩下的写入datatable
                        else
                            dr[j - 1] = worksheet.Cells[i, j].Text;
                    }
                }
                return dt;
            }
        }

        public static (List<T> DataList, List<ExcelAttribute> ExcelColumns, List<string> Errors) ToList<T>(string filePath, bool strict = false, IDictionary<string, List<SelectListItem>> drops = null)
        {
            return ToList<T>(filePath, Headers<T>(), strict, drops);
        }

        public static (List<T> DataList, List<ExcelAttribute> ExcelColumns, List<string> Errors) ToList<T>(string filePath, List<ExcelAttribute> headers, bool strict = true, IDictionary<string, List<SelectListItem>> drops = null)
        {
            using (ExcelPackage package = new ExcelPackage(new FileInfo(filePath)))
            {
                ExcelWorksheet worksheet = package.Workbook.Worksheets.First(t => t.Hidden == eWorkSheetHidden.Visible);
                return ToList<T>(worksheet, headers, strict, drops);
            }
        }

        /// <summary>
        /// 导入
        /// </summary>
        private static (List<T> DataList, List<ExcelAttribute> ExcelColumns, List<string> Errors) ToList<T>(ExcelWorksheet worksheet, List<ExcelAttribute> all_headers, bool strict = true, IDictionary<string, List<SelectListItem>> drops = null)
        {
            all_headers = all_headers.Where(t => t.Import).ToList();
            // 获取Excel列名对应的index;
            for (var i = 1; i <= worksheet.Dimension.End.Column; i++)
            {
                var headerName = worksheet.Cells[1, i].Text.Trim();
                var findHeaders = all_headers.Where(p => p.HeaderName == headerName || p.HeaderNames.Contains(headerName));                
                foreach (var h in findHeaders)
                {
                    if (h.Sort < 1) h.Sort = i;
                }
            }
            var headers = all_headers;
            if (strict)
            {
                var notFind = all_headers.Where(t => t.Sort < 1);
                if (notFind.Any())
                {
                    throw new Exception(string.Format("模板错误.未找到列 ‘{0}’", string.Join(",", notFind.Select(t => t.HeaderName))));
                }
            }
            else 
            {
                headers = all_headers.Where(t => t.Sort > 0).ToList();
            }
            var t_type = typeof(T);
            // 检查Property;
            headers.ForEach(h =>
            {
                if (h.Property == null) h.Property = t_type.GetProperty(h.PropertyName);
            });
            List<T> result = new List<T>();
            List<string> errors = new List<string>();
            Dictionary<string,string> error_columns = new Dictionary<string, string>();
            // 读取行;
            for (var r = 2; r <= worksheet.Dimension.End.Row; r++)
            {
                T temp = (T)Activator.CreateInstance(t_type);
                foreach (var header in headers)
                {
                    object obj = null;
                    var cell = worksheet.Cells[r, header.Sort];                    
                    var cell_string = cell.Text.Trim();
                    if (header.Property.PropertyType.IsEnum)
                    {
                        try
                        {
                            if (string.IsNullOrWhiteSpace(cell_string))
                            {
                                obj = header.Property.PropertyType.IsValueType ? Activator.CreateInstance(header.Property.PropertyType) : null;
                            }
                            else 
                            {
                                obj = Enum.Parse(header.Property.PropertyType, cell_string);
                            }
                        }
                        catch
                        {
                            var key = ToName(header.Sort);
                            error_columns.TryAdd(key, string.Format("{0}({1})列, 选项错误.", header.HeaderName, key));
                            errors.Add(string.Format("第{0}行,{1}({2})列(内容为：{3}), 选项错误.", r, header.HeaderName, key, cell_string));
                            //throw new Exception();
                        }
                    }
                    else
                    {
                        if (!string.IsNullOrWhiteSpace(header.Drop) && !string.IsNullOrWhiteSpace(cell_string)) 
                        {
                            var item = drops[header.Drop].Where(t => t.Text == cell_string).FirstOrDefault();
                            if (item == null)
                            {
                                var key = ToName(header.Sort);
                                error_columns.TryAdd(key, string.Format("{0}({1})列, 选项错误.", header.HeaderName, key));
                                errors.Add(string.Format("第{0}行,{1}({2})列(内容：{3}), 选项错误.", r, header.HeaderName, key, cell_string));
                                continue;
                                //throw new Exception();
                            }
                            else
                            {
                                cell_string = item.Value;
                            }
                            
                        }
                        switch (Type.GetTypeCode(header.Property.PropertyType))
                        {
                            case TypeCode.Int32:
                                if (string.IsNullOrWhiteSpace(cell_string))
                                {
                                    obj = 0;
                                }
                                else
                                {
                                    if (int.TryParse(cell_string, out int v))
                                    {
                                        obj = v;
                                    }
                                    else
                                    {
                                        var key = ToName(header.Sort);
                                        error_columns.TryAdd(key, string.Format("{0}({1})列, 应该是整数类型.", header.HeaderName, key));
                                        errors.Add(string.Format("第{0}行,{1}({2})列(内容：{3}),应该是整数类型.", r, header.HeaderName, key, cell_string));
                                        //throw new Exception();
                                    }
                                    // Convert.ToInt32(cell.Value)
                                }
                                break;
                            case TypeCode.Int64:
                                if (string.IsNullOrWhiteSpace(cell_string))
                                {
                                    obj = 0L;
                                }
                                else
                                {
                                    if (long.TryParse(cell_string, out long v))
                                    {
                                        obj = v;
                                    }
                                    else
                                    {
                                        var key = ToName(header.Sort);
                                        error_columns.TryAdd(key, string.Format("{0}({1})列, 应该是整数类型.", header.HeaderName, key));
                                        errors.Add(string.Format("第{0}行,{1}({2})列(内容：{3}),应该是整数类型.", r, header.HeaderName, key, cell_string));
                                        //throw new Exception(string.Format("第{0}行,{1}({2})列,应该是整数类型.", r, header.HeaderName, ToName(header.Sort)));
                                    }
                                }
                                break;
                            case TypeCode.Decimal:
                                if (string.IsNullOrWhiteSpace(cell_string))
                                {
                                    obj = 0M;
                                }
                                else
                                {
                                    cell_string = cell_string.Replace("元", "");
                                    if (decimal.TryParse(cell_string, out decimal v))
                                    {
                                        obj = v;
                                    }
                                    else
                                    {
                                        var key = ToName(header.Sort);
                                        error_columns.TryAdd(key, string.Format("{0}({1})列, 应该是数字类型.", header.HeaderName, key));
                                        errors.Add(string.Format("第{0}行,{1}({2})列(内容：{3}),应该是数字类型.", r, header.HeaderName, key, cell_string));
                                        //throw new Exception(string.Format("第{0}行,{1}({2})列,应该是数字类型.", r, header.HeaderName, ToName(header.Sort)));
                                    }
                                }
                                break;
                            case TypeCode.String:
                                obj = cell_string;
                                break;
                            case TypeCode.DateTime:
                                if (string.IsNullOrWhiteSpace(cell_string))
                                {
                                    obj = DateTime.MinValue;
                                }
                                else
                                {
                                    var cell_string_dt = cell_string.Replace("：", ":")
                                        //.Replace("上午"," ")
                                        .Replace("点",":").Replace("时",":").Replace("分","");
                                    //if (cell_string_dt.Contains("下午"))
                                    //{
                                    //    var sp_xia = cell_string_dt.Split("下午");
                                    //    var sp_xia2 = sp_xia[1].Trim().Split(":");
                                    //    var hh = int.Parse(sp_xia2[0]);
                                    //    if (hh <= 12)
                                    //    {
                                    //        cell_string_dt = sp_xia[0].Trim() + " " + (hh + 12).ToString() + sp_xia2[1];
                                    //    }
                                    //    else
                                    //    {
                                    //        cell_string_dt = sp_xia[0].Trim() + " " + (hh).ToString() + sp_xia2[1];
                                    //    }
                                    //}
                                    // https://docs.microsoft.com/zh-cn/dotnet/api/system.datetime.tryparse?view=net-5.0
                                    CultureInfo culture = CultureInfo.CreateSpecificCulture("zh-CN");
                                    if (DateTime.TryParse(cell_string_dt, culture, DateTimeStyles.None, out DateTime v))
                                    {
                                        obj = v;
                                    }
                                    //else if () 
                                    //{ 
                                    //}
                                    else
                                    {
                                        var val = cell.Style.Numberformat.Format;
                                        var notDateTime = true;
                                        if (cell.Value.GetType() == typeof(double))
                                        {
                                            try
                                            {
                                                obj = DateTime.FromOADate((double)cell.Value);
                                                notDateTime = false;
                                            }
                                            catch { }
                                        }
                                        if (notDateTime)
                                        {
                                            var key = ToName(header.Sort);
                                            error_columns.TryAdd(key, string.Format("{0}({1})列, 应该是时间格式.", header.HeaderName, key));
                                            errors.Add(string.Format("第{0}行,{1}({2})列(内容：{3}),应该是时间格式.", r, header.HeaderName, key, cell_string));
                                        }
                                        //throw new Exception(string.Format("第{0}行,{1}({2})列,应该是时间格式.", r, header.HeaderName, ToName(header.Sort)));
                                    }
                                }
                                break;
                            case TypeCode.Boolean:
                                if (cell_string == "是" || cell_string.ToLower() == "true" || cell_string == "1" || cell_string == "有")
                                {
                                    obj = true;
                                }
                                else
                                {
                                    obj = false;
                                }
                                break;
                            default:
                                try
                                {
                                    obj = Convert.ChangeType(cell_string, header.Property.PropertyType);
                                }
                                catch
                                {
                                    var key = ToName(header.Sort);
                                    error_columns.TryAdd(key, string.Format("{0}({1})列, 数据类型错误({2}).", header.HeaderName, key, header.Property.PropertyType));
                                    errors.Add(string.Format("第{0}行,{1}({2})列(内容：{3}),数据类型错误({4}).", r, header.HeaderName, key, cell_string, header.Property.PropertyType));
                                    //throw new Exception(string.Format("第{0}行,{1}({2})列,数据类型错误({3}).", r, header.HeaderName, ToName(header.Sort), header.Property.PropertyType));
                                }
                                break;
                                /*
                                 /// 
                                /// 设置类中的属性值
                                /// 
                                public bool SetModelValue(string FieldName,string Value, object obj)
                                {
                                    try
                                    {
                                        Type Ts = obj.GetType();
                                        object v = Convert.ChangeType(Value, Ts.GetProperty(FieldName).PropertyType);
                                        Ts.GetProperty(FieldName).SetValue(obj, v, null);
                                        return true;
                                    }
                                    catch
                                    {
                                        return false;
                                    }
                                }
                                /// 
                                /// 获取类中的属性值
                                /// 
                                public string GetModelValue(string FieldName, object obj)
                                {
                                    try
                                    {
                                        Type Ts = obj.GetType();
                                        object o = Ts.GetProperty(FieldName).GetValue(obj, null);
                                        string Value = Convert.ToString(o);
                                        if (string.IsNullOrEmpty(Value)) return null;
                                        return Value;
                                    }
                                    catch
                                    {
                                        return null;
                                    }
                                }    
                                 */

                        }
                    }
                    try 
                    {
                        header.Property.SetValue(temp, obj);
                    }
                    catch (Exception e) 
                    {
                        var key = ToName(header.Sort);
                        error_columns.TryAdd(key, string.Format("{0}({1})列, 转换失败({2}).", header.HeaderName, key, e.Message));
                        errors.Add(string.Format("第{0}行,{1}({2})列(内容：{3}),转换失败({4}).", r, header.HeaderName, key, cell_string, e.Message));
                        //throw new Exception(string.Format("第{0}行,{1}({2})列,转换失败({3}).", r, header.HeaderName, ToName(header.Sort), e.Message));
                    }
                    
                }
                result.Add(temp);
            }
            if (errors.Count > 0)
            {
                errors.InsertRange(0, error_columns.Select(t=>t.Value).ToList());
            }
            return (result, headers, errors);

        }

        public static byte[] Save<T>(List<T> data, IDictionary<string, List<SelectListItem>> drops = null)
        {
            using (ExcelPackage package = new ExcelPackage())
            {
                Save(package,data, Headers<T>(), drops);
                //package.SaveAs(stream);
                return package.GetAsByteArray();
            }
        }

        public static void Save<T>(FileInfo fileInfo, List<T> data, IDictionary<string, List<SelectListItem>> drops = null)
        {
            using (ExcelPackage package = new ExcelPackage())
            {
                Save(package, data, Headers<T>(), drops);
                package.SaveAs(fileInfo);
            }
        }

        public static void Save<T>(FileInfo fileInfo, List<T> data,IEnumerable<string> columns = null, IDictionary<string, List<SelectListItem>> drops = null, ExcelColumnType columnType = 0)
        {
            using (ExcelPackage package = new ExcelPackage())
            {
                var all_headers = Headers<T>().Where(t => t.Export).ToList();
                if (columns == null || columns.Count() == 0)
                {
                    if (columnType > 0) 
                    {
                        all_headers = all_headers.Where(t => t.ColumnType == columnType).ToList();
                    }
                    Save(package, data, all_headers, drops);
                }
                else
                {
                    List<ExcelAttribute> headers = new List<ExcelAttribute>();
                    foreach (var col in columns)
                    {
                        var colHeader = all_headers.First(t => t.PropertyName == col);
                        colHeader.Sort = 0;
                        headers.Add(colHeader);
                    }
                    Save(package, data, headers, drops);
                }
                package.SaveAs(fileInfo);
            }
        }

        public static ExcelPackage Save<T>(ExcelPackage package, List<T> data, List<ExcelAttribute> headers, IDictionary<string, List<SelectListItem>> drops = null)
        {
            var t_type = typeof(T);
            // 获取头部.
            if (headers.Any(t => t.Sort > 0))
            {
                // 如果存在排序号, 则排序.
                headers = headers.OrderBy(t => t.Sort).ToList();
            }
            // 检查Property;
            headers.ForEach(h =>
            {
                if (h.Property == null) h.Property = t_type.GetProperty(h.PropertyName);
            });
            // 每个字段所在的列号;
            var columnIndexDic = new Dictionary<string, int>();
            // 下拉框;
            var dropSheetDic = new Dictionary<string,(int Column,int Length)>();
            ExcelWorksheet dropSheet = null; 
            var worksheet = package.Workbook.Worksheets.Add("Sheet1");
            // 头部.
            for (var y = 0; y < headers.Count; y++)
            {
                var header = headers[y];
                var c = y + 1;// 列号
                columnIndexDic.Add(header.PropertyName, c);
                worksheet.Cells[1, c].Value = header.HeaderName;
                // 字体.
                worksheet.Cells[1, c].Style.Font.Bold = true;
                // 必填;
                if (header.Required) worksheet.Cells[1, c].Style.Font.Color.SetColor(Color.Red);
                // 有列的地址
                var address_columns = worksheet.Cells[2, c, ExcelPackage.MaxRows, c].Address;
                // 下拉列表
                List<SelectListItem> drop = null;
                if (!string.IsNullOrEmpty(header.Drop))
                {
                    drop = drops[header.Drop];
                }
                else if (header.Property.PropertyType.IsEnum)
                {
                    drop = Tools.EnumList(header.Property.PropertyType, false);
                }
                if (drop != null)
                {
                    var listArea = worksheet.DataValidations.AddListValidation(address_columns);
                    listArea.ShowErrorMessage = true;
                    listArea.Error = "请输入正确的选项!";
                    var dropTexts = drop.Select(t=>t.Text).ToList();
                    if(string.Join(",", dropTexts).Length > 255) 
                    {
                        if(dropSheet==null)
                        {
                            dropSheet = package.Workbook.Worksheets.Add("DropSheet");
                            dropSheet.Hidden = eWorkSheetHidden.VeryHidden;
                        }
                        var r = dropSheetDic.Keys.Count+1;
                        if(dropSheetDic.ContainsKey(header.Drop)){
                            r = dropSheetDic[header.Drop].Column;
                        }else{
                            dropSheetDic.Add(header.Drop, (r,dropTexts.Count));
                        }
                        var r_c = 0;
                        dropTexts.ForEach(txt => {
                            r_c++;
                            dropSheet.Cells[r_c, r].Value = txt;
                        });
                        listArea.Formula.ExcelFormula = string.Format("=DropSheet!${0}${1}:${2}${3}", ToName(r), 1, ToName(r), r_c);
                    }
                    else
                    {
                        dropTexts.ForEach(t => listArea.Formula.Values.Add(t));
                        //drop.ForEach(t => listArea.Formula.Values.Add(t.Text));
                        //val.Formula.ExcelFormula = string.Format("=DropDownList!${0}${1}:${2}${3}", char1, num1, char2, num2);
                        //val.ShowErrorMessage = true;
                        //val.Error = "Select from List of Values ...";
                    }
                }
            }
            // https://riptutorial.com/epplus/example/26056/number-formatting
            // https://riptutorial.com/epplus/example/26058/date-formatting
            // Text Format worksheet.Cells["A1:A25"].Style.Numberformat.Format = "@";
            // 数据.
            for (var i = 0; i < data.Count; i++)
            {
                for (var y = 0; y < headers.Count; y++)
                {
                    var c = y + 1;// 列号
                    var header = headers[y];
                    //object val;
                    //if (header.Property.PropertyType.IsEnum)
                    //{
                    //    object o = header.Property.GetValue(data[i], null);
                    //    var o_val = Convert.ToString(o, new FormatProvider(header.Format));
                    //    if (o_val == "0") 
                    //    {
                    //        // 处理枚举不是从0开始;
                    //        o_val = "";
                    //    }
                    //    val = o_val;
                    //}
                    //else
                    //{
                    //    switch (Type.GetTypeCode(header.Property.PropertyType))
                    //    {
                    //        case TypeCode.DateTime:
                    //            break;
                    //        case TypeCode.Boolean:
                    //            break;
                    //        default:
                    //            object o = header.Property.GetValue(data[i], null);
                    //            val = Convert.ToString(header.Property.GetValue(data[i], null), new FormatProvider(header.Format));
                    //            break;
                    //    }
                    //}
                    // 公式
                    if (!string.IsNullOrEmpty(header.Formula))
                    {
                        string pattern = @"(\{.*?\})";
                        Regex rgx = new Regex(pattern);
                        string resultFormula = header.Formula;// rgx.Replace(header.Formula, "AAA");                        
                        foreach (Match m in rgx.Matches(resultFormula))
                        {
                            var reg_col_name = m.Groups[0].ToString();
                            var prop_col_name = reg_col_name.TrimStart('{').TrimEnd('}');
                            var prop_col_index = columnIndexDic[prop_col_name];
                            resultFormula = Regex.Replace(resultFormula, reg_col_name, string.Format("{0}{1}", ToName(prop_col_index), i + 2));
                        }
                        worksheet.Cells[i + 2, c].Formula = resultFormula;
                    }
                    else
                    // 数据
                    if (!string.IsNullOrWhiteSpace(header.Drop))
                    {
                        //var v_s = string.Empty;
                        //var item = drops[header.Drop].Where(t => t.Value == header.Property.GetValue(data[i], null).ToString()).FirstOrDefault();
                        //if (item != null)
                        //{
                        //    v_s = item.Text;
                        //}
                        //worksheet.Cells[i + 2, c].Value = v_s;
                        worksheet.Cells[i + 2, c].Value = Tools.ToString(header.Property.GetValue(data[i], null), header.Drop, true);
                    }
                    else 
                    {
                        worksheet.Cells[i + 2, c].Value = Tools.ToString(header.Property.GetValue(data[i], null), header.Format);
                    }
                }
            }
            return package;
        }
        //Excel列字母转数字
        public static int ToIndex(string columnName,int start = 1)
        {
            if (!Regex.IsMatch(columnName.ToUpper(), @"[A-Z]+")) { throw new Exception("invalid parameter"); }

            int index = 0;
            char[] chars = columnName.ToUpper().ToCharArray();
            for (int i = 0; i < chars.Length; i++)
            {
                index += ((int)chars[i] - (int)'A' + 1) * (int)Math.Pow(26, chars.Length - i - 1);
            }
            return index - 1 + start;
        }

        //Excel数字转列字母
        public static string ToName(int index,int start = 1)
        {
            index = index - start;
            if (index < 0) { throw new Exception("invalid parameter"); }

            List<string> chars = new List<string>();
            do
            {
                if (chars.Count > 0) index--;
                chars.Insert(0, ((char)(index % 26 + (int)'A')).ToString());
                index = (int)((index - index % 26) / 26);
            } while (index > 0);

            return String.Join(string.Empty, chars.ToArray());
        }
    }
}
